"""
监控相关的API路由

提供系统健康检查、性能指标和各种监控端点
"""
from fastapi import APIRouter, Depends, Request, BackgroundTasks
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import text, select, and_, desc
from typing import Optional
import psutil
import platform
import time
import os
import datetime

from bot_api_v1.app.core.schemas import BaseResponse
from bot_api_v1.app.db.session import get_db
from bot_api_v1.app.core.logger import logger
from bot_api_v1.app.utils.decorators.tollgate import TollgateConfig
from bot_api_v1.app.tasks.base import get_task_statistics

router = APIRouter(tags=["监控"])

@router.get(
    "/health/detailed", 
    response_model=BaseResponse,
    description="详细的系统健康状态",
    summary="获取系统详细健康状态"
)
@TollgateConfig(
    title="详细健康检查", 
    type="health",
    base_tollgate="10",
    current_tollgate="1",
    plat="system"
)
async def detailed_health_check(
    request: Request,
    db: AsyncSession = Depends(get_db),
    bg_tasks: BackgroundTasks = None  # 为 bg_tasks 提供默认值 None
):
    """
    详细的系统健康检查
    
    检查所有系统组件的健康状态，包括:
    - 数据库连接
    - 文件系统
    - 内存使用
    - CPU使用
    - 异步任务状态
    """
    start_time = time.time()
    components = {}
    has_error = False
    
    # 检查数据库连接
    try:
        # 使用 scalar_one_or_none() 替代 fetchone()
        result = await db.scalar(text("SELECT 1"))
        components["database"] = {
            "status": "healthy" if result is not None else "error",
            "details": {
                "result": result
            }
        }
    except Exception as e:
        logger.error(f"数据库健康检查异常: {str(e)}")
        components["database"] = {
            "status": "error",
            "details": {
                "error": str(e)
            }
        }
        has_error = True
    
    # 检查文件系统
    try:
        disk = psutil.disk_usage('/')
        components["disk"] = {
            "status": "healthy" if disk.percent < 90 else "warning",
            "details": {
                "total_gb": round(disk.total / (1024**3), 2),
                "used_gb": round(disk.used / (1024**3), 2),
                "free_gb": round(disk.free / (1024**3), 2),
                "percent": disk.percent
            }
        }
        if disk.percent > 95:
            components["disk"]["status"] = "error"
            has_error = True
    except Exception as e:
        logger.error(f"文件系统健康检查异常: {str(e)}")
        components["disk"] = {
            "status": "error",
            "details": {
                "error": str(e)
            }
        }
        has_error = True
    
    # 检查内存
    try:
        memory = psutil.virtual_memory()
        components["memory"] = {
            "status": "healthy" if memory.percent < 90 else "warning",
            "details": {
                "total_gb": round(memory.total / (1024**3), 2),
                "available_gb": round(memory.available / (1024**3), 2),
                "used_gb": round(memory.used / (1024**3), 2),
                "percent": memory.percent
            }
        }
        if memory.percent > 95:
            components["memory"]["status"] = "error"
            has_error = True
    except Exception as e:
        logger.error(f"内存健康检查异常: {str(e)}")
        components["memory"] = {
            "status": "error",
            "details": {
                "error": str(e)
            }
        }
        has_error = True
    
    # 检查CPU
    try:
        cpu_percent = psutil.cpu_percent(interval=0.5)
        components["cpu"] = {
            "status": "healthy" if cpu_percent < 80 else "warning",
            "details": {
                "percent": cpu_percent,
                "count": psutil.cpu_count(),
                "load_avg": os.getloadavg() if hasattr(os, 'getloadavg') else None
            }
        }
        if cpu_percent > 95:
            components["cpu"]["status"] = "error"
            has_error = True
    except Exception as e:
        logger.error(f"CPU健康检查异常: {str(e)}")
        components["cpu"] = {
            "status": "error",
            "details": {
                "error": str(e)
            }
        }
        has_error = True
    
    # 检查异步任务状态
    try:
        task_stats = await get_task_statistics()
        components["tasks"] = {
            "status": "healthy",
            "details": task_stats
        }
        
        # 如果有太多正在运行的任务，可能表示问题
        running_tasks = task_stats.get("status_counts", {}).get("running", 0)
        if running_tasks > 100:
            components["tasks"]["status"] = "warning"
        if running_tasks > 500:
            components["tasks"]["status"] = "error"
            has_error = True
    except Exception as e:
        logger.error(f"任务健康检查异常: {str(e)}")
        components["tasks"] = {
            "status": "error",
            "details": {
                "error": str(e)
            }
        }
        has_error = True
    
    # 系统信息
    system_info = {
        "version": settings.VERSION,
        "environment": settings.ENVIRONMENT,
        "os": platform.system(),
        "python_version": platform.python_version(),
        "hostname": platform.node(),
        "response_time": f"{(time.time() - start_time) * 1000:.2f}ms",
        "startup_time": getattr(request.app.state, "startup_time", None),
        "uptime_seconds": round(time.time() - getattr(request.app.state, "startup_time", time.time())),
    }
    
    # 确定整体状态
    if has_error:
        status = "error"
        status_code = 503
        message = "系统出现错误"
    elif any(c["status"] == "warning" for c in components.values()):
        status = "warning"
        status_code = 200
        message = "系统运行中，但有潜在问题"
    else:
        status = "healthy"
        status_code = 200
        message = "系统运行正常"
    
    # 如果有后台任务，可以异步记录健康检查结果
    if bg_tasks:
        bg_tasks.add_task(
            log_health_check_result, 
            status=status, 
            components=components
        )
    
    return BaseResponse(
        code=status_code,
        message=message,
        data={
            "status": status,
            "components": components,
            "system": system_info,
            "timestamp": datetime.datetime.now().isoformat()
        }
    )

@router.get(
    "/metrics/summary", 
    response_model=BaseResponse,
    description="系统指标摘要",
    summary="获取系统指标摘要"
)
@TollgateConfig(
    title="系统指标摘要", 
    type="metrics",
    base_tollgate="10",
    current_tollgate="1",
    plat="system"
)
async def metrics_summary(
    request: Request,
    db: AsyncSession = Depends(get_db)
):
    """
    获取系统指标摘要
    
    包括:
    - CPU使用率
    - 内存使用率
    - 磁盘使用率
    - 数据库连接池状态
    - 请求速率
    - 错误率
    """
    try:
        # 收集基本性能指标
        cpu_percent = psutil.cpu_percent(interval=0.5)
        memory = psutil.virtual_memory()
        disk = psutil.disk_usage('/')
        
        # 尝试从请求应用状态获取请求计数器
        request_counts = getattr(request.app.state, "request_counts", {})
        
        # 收集数据库连接池信息
        db_stats = {}
        try:
            if hasattr(db.get_bind(), "pool"):
                pool = db.get_bind().pool
                db_stats = {
                    "pool_size": pool.size(),
                    "checkedout": pool.checkedout(),
                    "overflow": pool.overflow(),
                    "checkedin": pool.checkedin()
                }
        except Exception as e:
            logger.error(f"获取数据库统计信息失败: {str(e)}")
            db_stats = {"error": str(e)}
        
        # 构建响应
        return BaseResponse(
            code=200,
            message="成功获取系统指标摘要",
            data={
                "system": {
                    "cpu_percent": cpu_percent,
                    "memory_percent": memory.percent,
                    "memory_available_gb": round(memory.available / (1024**3), 2),
                    "disk_percent": disk.percent,
                    "disk_free_gb": round(disk.free / (1024**3), 2),
                    "process_count": len(psutil.pids()),
                    "open_files": len(psutil.Process().open_files()),
                    "connections": len(psutil.Process().connections())
                },
                "database": db_stats,
                "requests": request_counts,
                "tasks": await get_task_statistics(),
                "timestamp": datetime.datetime.now().isoformat()
            }
        )
    except Exception as e:
        logger.error(f"获取系统指标摘要失败: {str(e)}")
        return BaseResponse(
            code=500,
            message=f"获取系统指标摘要失败: {str(e)}",
            data=None
        )

async def log_health_check_result(status: str, components: dict):
    """
    记录健康检查结果
    
    Args:
        status: 整体健康状态
        components: 各组件的健康信息
    """
    try:
        log_level = "info" if status == "healthy" else "warning" if status == "warning" else "error"
        getattr(logger, log_level)(
            f"系统健康检查: {status}",
            extra={
                "health_check": {
                    "status": status,
                    "components": components
                }
            }
        )
    except Exception as e:
        logger.error(f"记录健康检查结果失败: {str(e)}")

# 导入设置
from bot_api_v1.app.core.config import settings
from bot_api_v1.app.models.log_trace import LogTrace
from datetime import datetime, timedelta


@router.get(
    "/rate-limit/config",
    response_model=BaseResponse,
    description="获取频率限制配置",
    summary="获取当前的频率限制配置"
)
@TollgateConfig(
    title="频率限制配置", 
    type="config",
    base_tollgate="10",
    current_tollgate="1",
    plat="system"
)
async def get_rate_limit_config(request: Request) -> BaseResponse:
    """
    获取当前的频率限制配置信息
    """
    try:
        # 检查访问权限（生产环境限制本地访问）
        if settings.ENVIRONMENT == "production":
            client_host = request.client.host if request.client else "unknown"
            if client_host not in ["127.0.0.1", "::1", "localhost"]:
                return BaseResponse(
                    code=403,
                    message="访问被拒绝：仅限管理员访问",
                    data=None
                )
        
        config_data = {
            "basic_limits": {
                "per_minute": settings.RATE_LIMIT_PER_MINUTE,
                "per_hour": getattr(settings, 'RATE_LIMIT_PER_HOUR', 1000),
                "per_day": getattr(settings, 'RATE_LIMIT_PER_DAY', 10000)
            },
            "vip_limits": {
                "per_minute": getattr(settings, 'VIP_RATE_LIMIT_PER_MINUTE', 200),
                "per_hour": getattr(settings, 'VIP_RATE_LIMIT_PER_HOUR', 5000),
                "per_day": getattr(settings, 'VIP_RATE_LIMIT_PER_DAY', 50000)
            },
            "strict_limits": {
                "per_minute": getattr(settings, 'STRICT_RATE_LIMIT_PER_MINUTE', 20),
                "per_hour": getattr(settings, 'STRICT_RATE_LIMIT_PER_HOUR', 200),
                "per_day": getattr(settings, 'STRICT_RATE_LIMIT_PER_DAY', 1000)
            },
            "ip_controls": {
                "whitelist_enabled": getattr(settings, 'IP_WHITELIST_ENABLED', True),
                "blacklist_enabled": getattr(settings, 'IP_BLACKLIST_ENABLED', True),
                "whitelist_count": len(getattr(settings, 'IP_WHITELIST', [])),
                "blacklist_count": len(getattr(settings, 'IP_BLACKLIST', []))
            },
            "redis_config": {
                "url_configured": bool(settings.REDIS_URL),
                "cache_url_configured": bool(getattr(settings, 'CACHE_REDIS_URL', None))
            },
            "environment": settings.ENVIRONMENT,
            "enhanced_middleware_enabled": True
        }
        
        return BaseResponse(
            code=200,
            message="成功获取频率限制配置",
            data=config_data
        )
        
    except Exception as e:
        logger.error(f"获取频率限制配置失败: {e}")
        return BaseResponse(
            code=500,
            message=f"获取频率限制配置时发生错误: {str(e)}",
            data=None
        )


@router.get(
    "/rate-limit/logs",
    response_model=BaseResponse,
    description="获取频率限制日志",
    summary="查看最近的频率限制和安全事件日志"
)
@TollgateConfig(
    title="频率限制日志查询", 
    type="logs",
    base_tollgate="10",
    current_tollgate="1",
    plat="system"
)
async def get_rate_limit_logs(
    request: Request,
    db: AsyncSession = Depends(get_db),
    hours: int = 24,  # 查询最近N小时的日志
    limit: int = 100,  # 限制返回数量
    ip_filter: Optional[str] = None  # IP过滤
) -> BaseResponse:
    """
    获取频率限制相关的日志记录
    
    Args:
        hours: 查询最近N小时的日志（默认24小时）
        limit: 返回记录数量限制（默认100条）
        ip_filter: 按IP地址过滤（可选）
    """
    try:
        # 检查访问权限
        if settings.ENVIRONMENT == "production":
            client_host = request.client.host if request.client else "unknown"
            if client_host not in ["127.0.0.1", "::1", "localhost"]:
                return BaseResponse(
                    code=403,
                    message="访问被拒绝：仅限管理员访问",
                    data=None
                )
        
        # 计算查询时间范围
        end_time = datetime.now()
        start_time = end_time - timedelta(hours=hours)
        
        # 构建查询条件 - 现在使用 extra 字段中的 event_type 来识别限流事件
        query_conditions = [
            LogTrace.created_at >= start_time,
            LogTrace.created_at <= end_time,
            LogTrace.para.has_key('event_type'),
            LogTrace.para['event_type'].in_([
                "rate_limit_exceeded", 
                "suspicious_ip_detected", 
                "blacklist_ip_blocked"
            ])
        ]
        
        # 添加IP过滤条件 - 现在IP在 para 字段的 client_ip 中
        if ip_filter:
            query_conditions.append(LogTrace.para['client_ip'].astext == ip_filter)
        
        # 执行查询
        stmt = select(LogTrace).where(
            and_(*query_conditions)
        ).order_by(desc(LogTrace.created_at)).limit(limit)
        
        result = await db.execute(stmt)
        logs = result.scalars().all()
        
        # 格式化日志数据
        log_data = []
        for log in logs:
            log_entry = {
                "id": str(log.id),
                "timestamp": log.created_at.isoformat(),
                "trace_key": log.trace_key,
                "event_type": log.para.get("event_type") if log.para else None,
                "level": log.level,
                "client_ip": log.para.get("client_ip") if log.para else None,
                "description": log.body,  # 主要的日志消息在 body 字段
                "details": log.para,
                "path": log.para.get("path") if log.para else None,
                "method": log.para.get("method") if log.para else None,
                "user_agent": log.para.get("user_agent") if log.para else None
            }
            log_data.append(log_entry)
        
        # 生成统计信息
        stats = {
            "total_events": len(log_data),
            "event_types": {},
            "top_ips": {},
            "time_range": {
                "start": start_time.isoformat(),
                "end": end_time.isoformat(),
                "hours": hours
            }
        }
        
        # 统计事件类型
        for log in logs:
            event_type = log.para.get("event_type") if log.para else "unknown"
            stats["event_types"][event_type] = stats["event_types"].get(event_type, 0) + 1
            
            # 统计IP频次
            client_ip = log.para.get("client_ip") if log.para else None
            if client_ip:
                stats["top_ips"][client_ip] = stats["top_ips"].get(client_ip, 0) + 1
        
        # 对IP按频次排序，取前10
        stats["top_ips"] = dict(sorted(stats["top_ips"].items(), key=lambda x: x[1], reverse=True)[:10])
        
        return BaseResponse(
            code=200,
            message=f"成功获取最近{hours}小时的频率限制日志",
            data={
                "logs": log_data,
                "statistics": stats,
                "query_params": {
                    "hours": hours,
                    "limit": limit,
                    "ip_filter": ip_filter
                }
            }
        )
        
    except Exception as e:
        logger.error(f"获取频率限制日志失败: {e}", exc_info=True)
        return BaseResponse(
            code=500,
            message=f"获取频率限制日志时发生错误: {str(e)}",
            data=None
        )


@router.get(
    "/referer-check/config",
    response_model=BaseResponse,
    description="获取Referer检查配置",
    summary="查看当前的Referer检查中间件配置"
)
@TollgateConfig(
    title="Referer检查配置", 
    type="config",
    base_tollgate="10",
    current_tollgate="1",
    plat="system"
)
async def get_referer_check_config(request: Request) -> BaseResponse:
    """
    获取Referer检查中间件的配置信息
    """
    try:
        # 检查访问权限
        if settings.ENVIRONMENT == "production":
            client_host = request.client.host if request.client else "unknown"
            if client_host not in ["127.0.0.1", "::1", "localhost"]:
                return BaseResponse(
                    code=403,
                    message="访问被拒绝：仅限管理员访问",
                    data=None
                )
        
        config_data = {
            "referer_check": {
                "enabled": getattr(settings, 'REFERER_CHECK_ENABLED', True),
                "strict_mode": getattr(settings, 'REFERER_CHECK_STRICT_MODE', False),
                "allow_empty_referer": getattr(settings, 'ALLOW_EMPTY_REFERER', True)
            },
            "allowed_domains": {
                "configured_domains": getattr(settings, 'ALLOWED_REFERER_DOMAINS', []),
                "project_domains": [
                    settings.DOMAIN_MAIN_URL,
                    settings.DOMAIN_API_URL,
                    settings.H5_FRONTEND_URL,
                ],
                "development_domains": [
                    'localhost', '127.0.0.1', 'localhost:8000', 'localhost:3000', 'localhost:5173'
                ] if settings.ENVIRONMENT == "development" else []
            },
            "protection_scope": {
                "check_patterns": ['/api/', '/media/', '/wechat/'],
                "exempt_patterns": ['/static/', '/docs', '/monitoring/health', '/robots.txt'],
                "check_methods": ['POST', 'PUT', 'PATCH', 'DELETE'],
                "exempt_methods": ['GET', 'HEAD', 'OPTIONS']
            },
            "environment": settings.ENVIRONMENT
        }
        
        return BaseResponse(
            code=200,
            message="成功获取Referer检查配置",
            data=config_data
        )
        
    except Exception as e:
        logger.error(f"获取Referer检查配置失败: {e}")
        return BaseResponse(
            code=500,
            message=f"获取Referer检查配置时发生错误: {str(e)}",
            data=None
        )


@router.get(
    "/referer-check/logs",
    response_model=BaseResponse,
    description="获取Referer检查违规日志",
    summary="查看Referer检查失败的日志记录"
)
@TollgateConfig(
    title="Referer检查日志", 
    type="logs",
    base_tollgate="10",
    current_tollgate="1",
    plat="system"
)
async def get_referer_check_logs(
    request: Request,
    db: AsyncSession = Depends(get_db),
    hours: int = 24,
    limit: int = 100,
    ip_filter: Optional[str] = None
) -> BaseResponse:
    """
    获取Referer检查相关的违规日志
    """
    try:
        # 检查访问权限
        if settings.ENVIRONMENT == "production":
            client_host = request.client.host if request.client else "unknown"
            if client_host not in ["127.0.0.1", "::1", "localhost"]:
                return BaseResponse(
                    code=403,
                    message="访问被拒绝：仅限管理员访问",
                    data=None
                )
        
        # 计算查询时间范围
        end_time = datetime.now()
        start_time = end_time - timedelta(hours=hours)
        
        # 构建查询条件
        query_conditions = [
            LogTrace.created_at >= start_time,
            LogTrace.created_at <= end_time,
            LogTrace.para.has_key('event_type'),
            LogTrace.para['event_type'].in_([
                "referer_check_failed", 
                "referer_check_warning"
            ])
        ]
        
        # 添加IP过滤条件
        if ip_filter:
            query_conditions.append(LogTrace.para['client_ip'].astext == ip_filter)
        
        # 执行查询
        stmt = select(LogTrace).where(
            and_(*query_conditions)
        ).order_by(desc(LogTrace.created_at)).limit(limit)
        
        result = await db.execute(stmt)
        logs = result.scalars().all()
        
        # 格式化日志数据
        log_data = []
        for log in logs:
            log_entry = {
                "id": str(log.id),
                "timestamp": log.created_at.isoformat(),
                "trace_key": log.trace_key,
                "event_type": log.para.get("event_type") if log.para else None,
                "level": log.level,
                "client_ip": log.para.get("client_ip") if log.para else None,
                "description": log.body,
                "details": log.para,
                "path": log.para.get("path") if log.para else None,
                "method": log.para.get("method") if log.para else None,
                "referer": log.para.get("referer") if log.para else None,
                "reason": log.para.get("reason") if log.para else None,
                "severity": log.para.get("severity") if log.para else None
            }
            log_data.append(log_entry)
        
        # 生成统计信息
        stats = {
            "total_events": len(log_data),
            "violation_reasons": {},
            "top_ips": {},
            "severity_levels": {},
            "time_range": {
                "start": start_time.isoformat(),
                "end": end_time.isoformat(),
                "hours": hours
            }
        }
        
        # 统计违规原因和IP
        for log in logs:
            reason = log.para.get("reason") if log.para else "unknown"
            severity = log.para.get("severity") if log.para else "unknown"
            client_ip = log.para.get("client_ip") if log.para else None
            
            stats["violation_reasons"][reason] = stats["violation_reasons"].get(reason, 0) + 1
            stats["severity_levels"][severity] = stats["severity_levels"].get(severity, 0) + 1
            
            if client_ip:
                stats["top_ips"][client_ip] = stats["top_ips"].get(client_ip, 0) + 1
        
        # 对IP按频次排序，取前10
        stats["top_ips"] = dict(sorted(stats["top_ips"].items(), key=lambda x: x[1], reverse=True)[:10])
        
        return BaseResponse(
            code=200,
            message=f"成功获取最近{hours}小时的Referer检查日志",
            data={
                "logs": log_data,
                "statistics": stats,
                "query_params": {
                    "hours": hours,
                    "limit": limit,
                    "ip_filter": ip_filter
                }
            }
        )
        
    except Exception as e:
        logger.error(f"获取Referer检查日志失败: {e}", exc_info=True)
        return BaseResponse(
            code=500,
            message=f"获取Referer检查日志时发生错误: {str(e)}",
            data=None
        )


# User-Agent过滤监控API
@router.get(
    "/user-agent-filter/config",
    response_model=BaseResponse,
    description="获取User-Agent过滤配置",
    summary="查看当前的User-Agent过滤中间件配置"
)
@TollgateConfig(
    title="User-Agent过滤配置", 
    type="config",
    base_tollgate="10",
    current_tollgate="1",
    plat="system"
)
async def get_user_agent_filter_config(request: Request) -> BaseResponse:
    """
    获取User-Agent过滤中间件配置信息
    """
    try:
        # 检查访问权限
        if settings.ENVIRONMENT == "production":
            client_host = request.client.host if request.client else "unknown"
            if client_host not in ["127.0.0.1", "::1", "localhost"]:
                return BaseResponse(
                    code=403,
                    message="访问被拒绝：仅限管理员访问",
                    data=None
                )
        
        # 获取应用实例中的中间件配置
        from bot_api_v1.app.middlewares.user_agent_filter import UserAgentFilterMiddleware
        
        # 创建临时实例获取配置（实际部署中可能需要不同的方式）
        temp_middleware = UserAgentFilterMiddleware(None)
        config_info = temp_middleware.get_configuration_info()
        
        # 从settings获取额外配置信息
        additional_config = {
            "blocked_patterns": getattr(settings, 'BLOCKED_USER_AGENT_PATTERNS', []),
            "allowed_patterns": getattr(settings, 'ALLOWED_USER_AGENT_PATTERNS', []),
            "environment": settings.ENVIRONMENT,
        }
        
        return BaseResponse(
            code=200,
            message="User-Agent过滤配置获取成功",
            data={
                "user_agent_filter": config_info,
                "patterns": additional_config,
                "status": "active" if config_info.get('enabled') else "disabled"
            }
        )
        
    except Exception as e:
        logger.error(f"获取User-Agent过滤配置失败: {str(e)}", exc_info=True)
        return BaseResponse(
            code=500,
            message=f"获取User-Agent过滤配置时发生错误: {str(e)}",
            data=None
        )


@router.get(
    "/user-agent-filter/logs",
    response_model=BaseResponse,
    description="获取User-Agent过滤日志",
    summary="查看User-Agent过滤中间件的日志记录"
)
@TollgateConfig(
    title="User-Agent过滤日志", 
    type="logs",
    base_tollgate="10",
    current_tollgate="1",
    plat="system"
)
async def get_user_agent_filter_logs(
    request: Request,
    db: AsyncSession = Depends(get_db),
    hours: int = 24,
    limit: int = 100,
    severity: Optional[str] = None,
    ip_filter: Optional[str] = None
) -> BaseResponse:
    """
    获取User-Agent过滤日志
    
    Args:
        hours: 查询最近N小时的日志（默认24小时）
        limit: 返回记录数量限制（默认100条）
        severity: 按严重程度筛选 (high/medium/low)
        ip_filter: 按IP地址过滤（可选）
    """
    try:
        # 检查访问权限
        if settings.ENVIRONMENT == "production":
            client_host = request.client.host if request.client else "unknown"
            if client_host not in ["127.0.0.1", "::1", "localhost"]:
                return BaseResponse(
                    code=403,
                    message="访问被拒绝：仅限管理员访问",
                    data=None
                )
        
        # 计算时间范围
        end_time = datetime.now()
        start_time = end_time - timedelta(hours=hours)
        
        # 构建基础查询条件
        query_conditions = [
            LogTrace.created_at >= start_time,
            LogTrace.created_at <= end_time,
            LogTrace.para.has_key('event_type'),
            LogTrace.para['event_type'].in_([
                'user_agent_blocked', 
                'user_agent_suspicious'
            ])
        ]
        
        # 添加严重程度过滤
        if severity:
            query_conditions.append(LogTrace.para['severity'].astext == severity)
        
        # 添加IP过滤条件
        if ip_filter:
            query_conditions.append(LogTrace.para['client_ip'].astext == ip_filter)
        
        # 执行查询
        stmt = select(LogTrace).where(
            and_(*query_conditions)
        ).order_by(desc(LogTrace.created_at)).limit(limit)
        
        result = await db.execute(stmt)
        logs = result.scalars().all()
        
        # 格式化日志数据
        log_data = []
        for log in logs:
            log_entry = {
                "id": str(log.id),
                "timestamp": log.created_at.isoformat(),
                "trace_key": log.trace_key,
                "event_type": log.para.get("event_type") if log.para else None,
                "level": log.level,
                "client_ip": log.para.get("client_ip") if log.para else None,
                "description": log.body,
                "details": log.para,
                "path": log.para.get("path") if log.para else None,
                "method": log.para.get("method") if log.para else None,
                "user_agent": log.para.get("user_agent", "")[:300] if log.para else "",  # 限制长度
                "matched_pattern": log.para.get("matched_pattern", "")[:100] if log.para else "",
                "reason": log.para.get("reason") if log.para else None,
                "severity": log.para.get("severity") if log.para else None,
                "action": log.para.get("action") if log.para else None
            }
            log_data.append(log_entry)
        
        # 生成统计信息
        stats = {
            "total_events": len(log_data),
            "violation_reasons": {},
            "top_ips": {},
            "severity_levels": {},
            "event_types": {},
            "time_range": {
                "start": start_time.isoformat(),
                "end": end_time.isoformat(),
                "hours": hours
            }
        }
        
        # 统计各种信息
        for log in logs:
            reason = log.para.get("reason") if log.para else "unknown"
            severity_level = log.para.get("severity") if log.para else "unknown"
            event_type = log.para.get("event_type") if log.para else "unknown"
            client_ip = log.para.get("client_ip") if log.para else None
            
            stats["violation_reasons"][reason] = stats["violation_reasons"].get(reason, 0) + 1
            stats["severity_levels"][severity_level] = stats["severity_levels"].get(severity_level, 0) + 1
            stats["event_types"][event_type] = stats["event_types"].get(event_type, 0) + 1
            
            if client_ip:
                stats["top_ips"][client_ip] = stats["top_ips"].get(client_ip, 0) + 1
        
        # 对IP按频次排序，取前10
        stats["top_ips"] = dict(sorted(stats["top_ips"].items(), key=lambda x: x[1], reverse=True)[:10])
        
        return BaseResponse(
            code=200,
            message=f"成功获取最近{hours}小时的User-Agent过滤日志",
            data={
                "logs": log_data,
                "statistics": stats,
                "query_params": {
                    "hours": hours,
                    "limit": limit,
                    "severity_filter": severity,
                    "ip_filter": ip_filter
                }
            }
        )
        
    except Exception as e:
        logger.error(f"获取User-Agent过滤日志失败: {e}", exc_info=True)
        return BaseResponse(
            code=500,
            message=f"获取User-Agent过滤日志时发生错误: {str(e)}",
            data=None
        )